﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Domain;
using System.Data;
using System.IO;
using System.Drawing.Imaging;
using System.Drawing;
namespace Repository
{
    public class ClienteDB : IQuery<Cliente>
    {

        private SqlConnection conn;

        public ClienteDB(SqlConnection conn)
        {
            this.conn = conn;
        }
        
        public bool save(Cliente e)
        {
            try
            {
                byte[] foto = e.FotoUsuario();

                SqlCommand command = new SqlCommand("INSERT INTO cliente (nome,telefone,celular,cpf,rg,ativo,senha,usuario,foto,email)VALUES('"+e.Nome+"','"+e.Telefone+"','"+e.Celular+"','"+e.Cpf+"','"+e.Rg+"','true','"+e.Senha+"','"+e.Usuario+"',@foto,'"+e.Email+"')",this.conn);
                SqlParameter paramFoto = new SqlParameter("@foto", SqlDbType.Binary);
                paramFoto.Value = foto;
                command.Parameters.Add(paramFoto);
                command.ExecuteNonQuery();
                return true;
            }
            catch (SqlException sqlex)
            {
                return false;
            }
        }

        public bool update(Cliente e)
        {
            try
            {
                SqlCommand command = new SqlCommand("update cliente set nome= '"+e.Nome+"',rg='"+e.Rg+"',cpf='"+e.Cpf+"',telefone='"+e.Telefone+"',celular='"+e.Celular+"',foto = '"+e.FotoUsuario()+"',senha='"+e.Senha+"','"+e.Usuario+"',email ='"+e.Email+"' ", this.conn);
                command.ExecuteNonQuery();
                return true;
            }
            catch (SqlException sqlex)
            {
                return false;
            }
        }

        public DataTable AuxDataGridViewClientes() 
        {
            try 
            {
                SqlCommand command = new SqlCommand("select * from cliente where ativo = 'true'",this.conn);
                SqlDataAdapter da = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }catch(SqlException sqlex) 
            {
                return null;
            }
        }

        public List<Cliente> getAllList()
        {
            try
            {
                SqlCommand command = new SqlCommand("select * from cliente", this.conn);
                SqlDataReader dr = command.ExecuteReader();
                List<Cliente> clientes = new List<Cliente>();

                while (dr.Read())
                {
                    Cliente p = new Cliente();
                    p.Id_cliente = Convert.ToInt32(dr["id_cliente"]);
                    p.Nome = Convert.ToString(dr["nome"]);
                    p.Telefone = Convert.ToString(dr["telefone"]);
                    p.Celular = Convert.ToString(dr["celular"]);
                    p.Cpf = Convert.ToString(dr["cpf"]);
                    p.Rg = Convert.ToString(dr["rg"]);
                    p.Email = Convert.ToString(dr["email"]);
                    try
                    {
                        p.FotoUsuario((byte[])dr["foto"]);
                    }
                    catch (Exception)
                    {
                        //obter a simagem defasult, transformar array de bytes e passar

                        string nomeArquivo = @"C:\Users\William Niedzweidz\Desktop\ECOM\Ecommerce\Ecommerce\Resources\_TempSemFoto.gif";
                        Bitmap bmp = new Bitmap(nomeArquivo);
                        MemoryStream ms = new MemoryStream();
                        bmp.Save(ms, ImageFormat.Jpeg);
                        byte[] foto = ms.ToArray();
                        p.FotoUsuario(foto);
                    }
                    p.Senha = Convert.ToString(dr["senha"]);
                    p.Usuario = Convert.ToString(dr["usuario"]);
                    clientes.Add(p);
                }
                return clientes;
            }
            catch (SqlException sqlex)
            {
                return null;
            }
        }

        public List<Cliente> getListById(int id)
        {
            try
            {
                SqlCommand command = new SqlCommand("select * from cliente where id_cliente = "+id+" ", this.conn);
                SqlDataReader dr = command.ExecuteReader();
                List<Cliente> clientes = new List<Cliente>();

                while (dr.Read())
                {
                    Cliente p = new Cliente();
                    p.Id_cliente = Convert.ToInt32(dr["id_cliente"]);
                    p.Nome = Convert.ToString(dr["nome"]);
                    p.Telefone = Convert.ToString(dr["telefone"]);
                    p.Celular = Convert.ToString(dr["celular"]);
                    p.Cpf = Convert.ToString(dr["cpf"]);
                    p.Rg = Convert.ToString(dr["rg"]);
                    p.Email = Convert.ToString(dr["email"]);
                    try
                    {
                        p.FotoUsuario((byte[])dr["foto"]);
                    }
                    catch (Exception)
                    {
                        //obter a simagem defasult, transformar array de bytes e passar

                        string nomeArquivo = @"C:\Users\William Niedzweidz\Desktop\ECOM\Ecommerce\Ecommerce\Resources\_TempSemFoto.gif";
                        Bitmap bmp = new Bitmap(nomeArquivo);
                        MemoryStream ms = new MemoryStream();
                        bmp.Save(ms, ImageFormat.Jpeg);
                        byte[] foto = ms.ToArray();
                        p.FotoUsuario(foto);
                    }
                    p.Senha = Convert.ToString(dr["senha"]);
                    p.Usuario = Convert.ToString(dr["usuario"]);
                    clientes.Add(p);
                }
                return clientes;
            }
            catch (SqlException sqlex)
            {
                return null;
            }
        }

        public Cliente Login(Cliente cliente)
        {
            try
            {
                SqlCommand command = new SqlCommand("select * from cliente where usuario = '"+cliente.Usuario+"' and senha ='"+cliente.Senha+"' ", this.conn);
                SqlDataReader dr = command.ExecuteReader();
                List<Cliente> clientes = new List<Cliente>();
     
                while (dr.Read())
                {
                    Cliente p = new Cliente();
                    p.Id_cliente = Convert.ToInt32(dr["id_cliente"]);
                    p.Nome = Convert.ToString(dr["nome"]);
                    p.Telefone = Convert.ToString(dr["telefone"]);
                    p.Celular = Convert.ToString(dr["celular"]);
                    p.Cpf = Convert.ToString(dr["cpf"]);
                    p.Rg = Convert.ToString(dr["rg"]);
                    p.Email = Convert.ToString(dr["email"]);
                    try
                    {
                        p.FotoUsuario((byte[])dr["foto"]);
                    }
                    catch (Exception)
                    {
                        //obter a simagem defasult, transformar array de bytes e passar

                        string nomeArquivo = @"C:\inetpub\wwwroot\Ecommerce\Ecommerce\Resources\_TempSemFoto.gif";
                        Bitmap bmp = new Bitmap(nomeArquivo);
                        MemoryStream ms = new MemoryStream();
                        bmp.Save(ms, ImageFormat.Jpeg);
                        byte[] foto = ms.ToArray();
                        p.FotoUsuario(foto);                    
                    }
      

                    p.Senha = Convert.ToString(dr["senha"]);
                    p.Usuario = Convert.ToString(dr["usuario"]);
                    clientes.Add(p);
                }

                for (int i = 0; clientes.Count > i; i++) 
                {
                    if (clientes[i].Senha.Equals(cliente.Senha) && clientes[i].Usuario.Equals(cliente.Usuario)) 
                    {
                        return clientes[i];
                    }
                }
                return null;
            }
            catch (SqlException sqlex)
            {
                return new Cliente();
            }
        }
        public bool delete(Cliente e)
        {
             try
            {
                SqlCommand command = new SqlCommand("delete from cliente where id_cliente = "+e.Id_cliente+" ", this.conn);
                command.ExecuteNonQuery();
                return true;
            }
            catch (SqlException sqlex)
            {
                return false;
            }
        }

        public void openConnection()
        {
            this.conn.Open();
        }

        public void closeConnection()
        {
            this.conn.Close();
        }


        public void putConnection(SqlConnection connection)
        {
            this.conn = connection;
        }
    }
}
